﻿GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Utility_Asset_Distribution_ImportData')
	DROP PROCEDURE up_Utility_Asset_Distribution_ImportData
GO


CREATE PROCEDURE [dbo].[up_Utility_Asset_Distribution_ImportData]
(
	@kmlFileName	varchar(255),
	@kmlContent		nvarchar(MAX),
	@utilityId		int
)
AS


DECLARE @kmlData as xml
SET @kmlData = CONVERT(xml, REPLACE(REPLACE(@kmlContent, 'xmlns="http://earth.google.com/kml/2.0"' , ''), '<?xml version="1.0" encoding="utf-8"?>', ''))

--Inserts in ImportedData table
declare @kmlContentTable table (kmlContent xml, UtilityKey nvarchar(255), Latitude float, Longitude float)
insert into @kmlContentTable
SELECT
	Item.query('.') as kmlContent
	,Item.query('.').value('(Placemark/name)[1]', 'nvarchar(max)') as UtilityKey
	,dbo.[fn_SplitAndSearch](Item.query('.').value('(Placemark/Point/coordinates)[1]', 'nvarchar(255)'), ',', 2) as Longitude
	,dbo.[fn_SplitAndSearch](Item.query('.').value('(Placemark/Point/coordinates)[1]', 'nvarchar(255)'), ',', 1) as Latitude
FROM
	@kmlData.nodes('/kml/Folder/Placemark') T(Item)

--Filters close poles
INSERT INTO tb_Utility_Asset_Distributions_ImportedData (Data)
SELECT 
	KmlContentTable.kmlContent
FROM
	@kmlContentTable AS KmlContentTable
WHERE
	NOT EXISTS (
			SELECT 1
			FROM tb_Utility_Asset_Distributions
			WHERE dbo.fn_DistanceBetween(	
					KmlContentTable.Latitude, 
					KmlContentTable.Longitude, 
					Latitude, 
					Longitude
					) < 6 --Filters close poles
			)

--begin tran
/*
declare @deleted table (id int)

print 'clear assets'

delete from tb_Utility_Asset_Distributions
output DELETED.Utility_Asset_DistributionId into @deleted
where CreatedBy = 'IMPORT'

print 'clear xref tops'

delete from tb_Utility_Asset_Distribution_TopsXPole
where Utility_Asset_DistributionFk in (select id from @deleted)

print 'clear xref orphans'

delete from tb_Utility_Asset_Distribution_TopsXPole
where Utility_Asset_DistributionFk not in (select Utility_Asset_DistributionId from tb_Utility_Asset_Distributions)
*/

begin tran

print 'prepare temp table'

declare @temp table (ImportedDataId int, UtilityKey varchar(255), Longitude float, Latitude float, Altitude float, Number varchar(255), [Year] int, Class varchar(255), Treatment varchar(255), Specie varchar(255))
insert into @temp
select
	 [Data].ImportedDataId 
	,Data.value('(Placemark/name)[1]', 'nvarchar(max)') as UtilityKey
	,dbo.[fn_SplitAndSearch](Data.value('(Placemark/Point/coordinates)[1]', 'nvarchar(255)'), ',', 1) as Longitude
	,dbo.[fn_SplitAndSearch](Data.value('(Placemark/Point/coordinates)[1]', 'nvarchar(255)'), ',', 2) as Latitude
	,dbo.[fn_SplitAndSearch](Data.value('(Placemark/Point/coordinates)[1]', 'nvarchar(255)'), ',', 3) as Altitude
	,dbo.[fn_SplitAndSearch](Data.value('(Placemark/description)[1]', 'nvarchar(max)'), '|', 1) as Number
	,CASE IsNumeric(dbo.[fn_SplitAndSearch](Data.value('(Placemark/description)[1]', 'nvarchar(max)'), '|', 8)) 
		WHEN 1 THEN CONVERT(int, dbo.[fn_SplitAndSearch](Data.value('(Placemark/description)[1]', 'nvarchar(max)'), '|', 8))
		ELSE NULL
	 END AS [Year]
	,dbo.[fn_SplitAndSearch](Data.value('(Placemark/description)[1]', 'nvarchar(max)'), '|', 9) as Class
	,dbo.[fn_SplitAndSearch](Data.value('(Placemark/description)[1]', 'nvarchar(max)'), '|', 15) as Treatment
	,dbo.[fn_SplitAndSearch](Data.value('(Placemark/description)[1]', 'nvarchar(max)'), '|', 19) as Specie
from 
	tb_Utility_Asset_Distributions_ImportedData as [Data]
where
	Utility_Asset_DistributionFk is null

declare @group varchar(255)
set @group = @kmlFileName

print 'prepare top types temp table'
declare @topsTemp table (UtilityKey nvarchar(255), TopName nvarchar(255))
insert into @topsTemp
select DISTINCT Tops.UtilityKey, Tops.TopTypeName
from (
	select 
		Data.value('(Placemark/name)[1]', 'nvarchar(255)') [UtilityKey],
		dbo.fn_splitandsearch(Data.value('(Placemark/description)[1]', 'nvarchar(max)'),'|',2) TopTypeName
	from 
		tb_Utility_Asset_Distributions_ImportedData
	union all
	select 
		Data.value('(Placemark/name)[1]', 'nvarchar(255)') [UtilityKey],
		dbo.fn_splitandsearch(Data.value('(Placemark/description)[1]', 'nvarchar(max)'),'|',3)  TopTypeName
	from 
		tb_Utility_Asset_Distributions_ImportedData
	union all
	select 
		Data.value('(Placemark/name)[1]', 'nvarchar(255)') [UtilityKey],
		dbo.fn_splitandsearch(Data.value('(Placemark/description)[1]', 'nvarchar(max)'),'|',4)  TopTypeName
	from 
		tb_Utility_Asset_Distributions_ImportedData
	union all
	select 
		Data.value('(Placemark/name)[1]', 'nvarchar(255)') [UtilityKey],
		dbo.fn_splitandsearch(Data.value('(Placemark/description)[1]', 'nvarchar(max)'),'|',5)  TopTypeName
	from 
		tb_Utility_Asset_Distributions_ImportedData
	union all
	select 
		Data.value('(Placemark/name)[1]', 'nvarchar(255)') [UtilityKey],
		dbo.fn_splitandsearch(Data.value('(Placemark/description)[1]', 'nvarchar(max)'),'|',6)  TopTypeName
	from 
		tb_Utility_Asset_Distributions_ImportedData
	union all
	select 
		Data.value('(Placemark/name)[1]', 'nvarchar(255)') [UtilityKey],
		dbo.fn_splitandsearch(Data.value('(Placemark/description)[1]', 'nvarchar(max)'),'|',7)  TopTypeName
	from 
		tb_Utility_Asset_Distributions_ImportedData
) Tops
where Tops.TopTypeName <> ''

print 'add assets'

declare @insertedAssetIds table (Utility_Asset_DistributionId int, UtilityKey varchar(255))

insert into tb_Utility_Asset_Distributions
(
	 UtilityFK
	,Latitude
	,Longitude
	,Altitude
	,Date
	,Number
	,[Year]
	,CreatedOn
	,CreatedBy
	,UtilityGroup
	,UtilityKey
)
OUTPUT INSERTED.Utility_Asset_DistributionId, INSERTED.UtilityKey INTO @insertedAssetIds
select DISTINCT
	 @UtilityId
	,Latitude
	,Longitude
	,Altitude
	,getdate()
	,Number
	,[Year]
	,getdate()
	,'IMPORT'
	,@group
	,UtilityKey
from 
	@temp as [Data]
where
	Data.Number is not null

print 'add missing top types'

insert tb_Distributions_TopTypes (Name)
select TopType 
from (
	select 
		DISTINCT TopName TopType
	from 
		@topsTemp 
) as WorkingTable 
where TopType not in (select Name from tb_Distributions_TopTypes) and TopType is not null

print 'add missing classes'

insert tb_Distribution_Classes (Name)
select Class
from (
	select 
		DISTINCT Class
	from 
		@temp as [Data]
) as WorkingTable 
where Class not in (select Name from tb_Distribution_Classes) and Class is not null and Class <> ''

print 'add missing treatments'

insert tb_XMissionStructures_PoleTreatments (Name)
select Treatment
from (
	select 
		DISTINCT Treatment
	from 
		@temp as [Data]
) as WorkingTable 
where Treatment not in (select Name from tb_XMissionStructures_PoleTreatments) and Treatment is not null and Treatment <> ''

print 'add missing species'

insert tb_XMissionStructures_PoleSpecies (Name)
select Specie
from (
	select 
		DISTINCT Specie
	from 
		@temp as [Data]
) as WorkingTable 
where Specie not in (select Name from tb_XMissionStructures_PoleSpecies) and Specie is not null and Specie <> ''

print 'add xref tops'

insert into tb_Utility_Asset_Distribution_TopsXPole
(
	 Utility_Asset_DistributionFk
	,TopTypeFk
)
select 
	 Assets.Utility_Asset_DistributionId
	,Tops.Distributions_TopTypeId
from 
	tb_Utility_Asset_Distributions Assets
	join @topsTemp as TopsData on Assets.UtilityKey = TopsData.UtilityKey 
	join tb_Distributions_TopTypes Tops on TopsData.TopName = Tops.Name
where 
	Assets.Utility_Asset_DistributionId not in 
	(select Utility_Asset_DistributionFk from tb_Utility_Asset_Distribution_TopsXPole)

print 'update xref class'

update tb_Utility_Asset_Distributions
set ClassFk = classes.ClassId
from tb_Distribution_Classes classes
join @temp Data
	on Data.Class = classes.[Name]
join tb_Utility_Asset_Distributions assets
	on assets.UtilityKey = Data.UtilityKey

print 'update xref treatment'

update tb_Utility_Asset_Distributions
set TreatmentFk = treatments.XMissionStructures_PoleTreatmentId
from tb_XMissionStructures_PoleTreatments treatments
join @temp Data
	on Data.Treatment = treatments.[Name]
join tb_Utility_Asset_Distributions assets
	on assets.UtilityKey = Data.UtilityKey

print 'update xref specie'

update tb_Utility_Asset_Distributions
set SpeciesFk = species.XMissionStructures_PoleSpeciesId
from tb_XMissionStructures_PoleSpecies species
join @temp Data
	on Data.Specie = species.[Name]
join tb_Utility_Asset_Distributions assets
	on assets.UtilityKey = Data.UtilityKey

print 'update imported data record asset fk'

UPDATE 
	tb_Utility_Asset_Distributions_ImportedData
SET 
	Utility_Asset_DistributionFk = [InsertedAssets].Utility_Asset_DistributionId
FROM 
	@insertedAssetIds [InsertedAssets]
WHERE 
	tb_Utility_Asset_Distributions_ImportedData.Data.value('(Placemark/name)[1]', 'nvarchar(max)') = [InsertedAssets].UtilityKey

print 'end'

commit

/*
SELECT * FROM tb_Utility_Asset_Distribution_TopsXPole
SELECT * FROM tb_Utility_Asset_Distributions
SELECT * FROM tb_Utility_Asset_Distributions_ImportedData
SELECT * FROM tb_Distribution_Classes
SELECT * FROM tb_XMissionStructures_PoleSpecies
SELECT * FROM tb_XMissionStructures_PoleTreatments

DELETE tb_Utility_Asset_Distribution_TopsXPole
DELETE tb_Utility_Asset_Distributions
DELETE tb_Utility_Asset_Distributions_ImportedData
DELETE tb_Distribution_Classes WHERE ClassId > 8

DECLARE @kmlContent nvarchar(max)
DECLARE @kmlFileName nvarchar(max)
DECLARE @utilityId int

SET @kmlFileName = 'San_Juan_Poles.kml'
SET @kmlContent = '<?xml version="1.0" encoding="utf-8"?><kml xmlns="http://earth.google.com/kml/2.0"><Folder><name>Shapefile Converter Generated File</name><open>1</open><Placemark><name>33608db6 1832 4293 825d a306fed9a7eb</name><description><![CDATA[32785|||||||||||||||||||]]></description><Point><coordinates>-107.82125970543,37.6694016531379,0</coordinates></Point></Placemark><Placemark><name>f12e3bf8 7955 48a9 b67c f0f5c5a4392c</name><description><![CDATA[31422|||||||||||||||||||]]></description><Point><coordinates>-107.816403863048,37.6649006108757,0</coordinates></Point></Placemark><Placemark><name>be3783c9 f7b1 47d4 b3c5 51ebb87edfc8</name><description><![CDATA[28768|A5.21||||||||||||||None||||]]></description><Point><coordinates>-107.82220886421,37.6708160668488,0</coordinates></Point></Placemark><Placemark><name>afe16560 c93e 4a8f 84c1 8e89da6a0028</name><description><![CDATA[28769|A1.11||||||||||||||Phone||||]]></description><Point><coordinates>-107.821634853713,37.6701433585241,0</coordinates></Point></Placemark><Placemark><name>3e778ce5 4725 4509 9a99 7cfd4cce005a</name><description><![CDATA[28770|A2.21||||||||||||||None||||]]></description><Point><coordinates>-107.820375869766,37.6685638662042,0</coordinates></Point></Placemark><Placemark><name>f456434c 5c62 41b4 a055 0c6b9499cae4</name><description><![CDATA[28771|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.819781247157,37.6683366038882,0</coordinates></Point></Placemark><Placemark><name>e05b9f17 86d7 4532 b173 9f1071c339ac</name><description><![CDATA[28772|A2.21||||||||||||||None||||]]></description><Point><coordinates>-107.819392231389,37.6680530820688,0</coordinates></Point></Placemark><Placemark><name>7e94fe15 6743 45a0 8714 cf9414ac9e28</name><description><![CDATA[28773|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.818649090044,37.6674963348353,0</coordinates></Point></Placemark><Placemark><name>c54c3eb3 c640 4963 b3e5 8a4e5bf25a35</name><description><![CDATA[28774|A6.2||||||||||||||Phone||||]]></description><Point><coordinates>-107.818007346457,37.66686615481,0</coordinates></Point></Placemark><Placemark><name>d2a17e51 0322 49b4 9aa3 18f5cce4ae06</name><description><![CDATA[28775|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.820811197711,37.6670113357188,0</coordinates></Point></Placemark><Placemark><name>871daca9 87af 410e b7ec 50189ef1e7cf</name><description><![CDATA[28776|A5.1|A5.1|||||||||||||Phone||||]]></description><Point><coordinates>-107.819838896789,37.6668629628414,0</coordinates></Point></Placemark><Placemark><name>12798b9c 38a9 46c6 92c3 42205de875bd</name><description><![CDATA[35450|A1.11||||||2008|5|||35||||Phone||||]]></description><Point><coordinates>-107.819482540153,37.6665183578707,0</coordinates></Point></Placemark><Placemark><name>e0be647c 100c 4fe0 9ebf b12d43a1c1d6</name><description><![CDATA[28778|A1.11||||||||||||||Phone||||]]></description><Point><coordinates>-107.81891964566,37.6661998072044,0</coordinates></Point></Placemark><Placemark><name>087109db 4f21 4535 81fc c464a9bf771e</name><description><![CDATA[28779|M5-5|M5-5|A5.21|A5.21|||||||||||Phone||||]]></description><Point><coordinates>-107.818113192582,37.6658283695856,0</coordinates></Point></Placemark><Placemark><name>541e3d23 2c6d 40e9 84c5 40e8c5e845bf</name><description><![CDATA[28780|A5.1||||||||||||||None||||]]></description><Point><coordinates>-107.822062383726,37.667236604025,0</coordinates></Point></Placemark><Placemark><name>774557a9 22ec 44a4 8c4d 88f28b424cb4</name><description><![CDATA[28781|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.821126928997,37.6693731894897,0</coordinates></Point></Placemark><Placemark><name>36579d01 a28d 416e a47d fc415c1f0a14</name><description><![CDATA[28782|A1.11|UM5-2||||||6|||30||||None|None||DF|]]></description><Point><coordinates>-107.82059785869,37.6690320003495,0</coordinates></Point></Placemark><Placemark><name>ca947c7e bc49 4948 b7d8 69030cc600c8</name><description><![CDATA[28783|A1.11|||||||6|||40||||||||]]></description><Point><coordinates>-107.818273752748,37.6670971568428,0</coordinates></Point></Placemark><Placemark><name>b18320ee 1765 4878 8ee8 048ef05d8282</name><description><![CDATA[28817|A5.1|A5.1|||||||||||||Phone||||]]></description><Point><coordinates>-107.817170293005,37.6666216859733,0</coordinates></Point></Placemark><Placemark><name>2de8aee3 d0e8 4400 9ef4 9fcae32d4ce3</name><description><![CDATA[28818|A1.2||||||||||||||None||||]]></description><Point><coordinates>-107.816811808772,37.6662084350393,0</coordinates></Point></Placemark><Placemark><name>9364cfd2 0418 492a af3d 3aeb7a6f8ab3</name><description><![CDATA[35451|A1.11||||||2008|5|||35||||Phone||||]]></description><Point><coordinates>-107.817692229049,37.665627889114,0</coordinates></Point></Placemark><Placemark><name>6f21873e ceb6 4a65 8418 c5fe1a10ddec</name><description><![CDATA[35452|A5.21||||||2008|5|||35||||None||||]]></description><Point><coordinates>-107.817164314688,37.6653223760987,0</coordinates></Point></Placemark><Placemark><name>b8b6c498 c549 487b 97fa 7a5643cb66d5</name><description><![CDATA[28821|M5-5|M5-5|A5.21|A1.11|||||||||||Phone||||]]></description><Point><coordinates>-107.817514743611,37.6654775476117,0</coordinates></Point></Placemark><Placemark><name>9ac6c5a0 5b93 4b65 bb6f da5c90e6e706</name><description><![CDATA[28822|M5-5|M5-5|A5.21|A1.11|||||||||||Phone||||]]></description><Point><coordinates>-107.816487352399,37.6657780287716,0</coordinates></Point></Placemark><Placemark><name>c47d74b8 435e 4539 8b75 25212647938e</name><description><![CDATA[33983|M5-5|M5-5|A5.21|A5.21|||2007|5|||35||||Phone||||]]></description><Point><coordinates>-107.815630758708,37.6660146952815,0</coordinates></Point></Placemark><Placemark><name>08e2fa28 490b 43fe bf4f 6ba759add16b</name><description><![CDATA[28825|||||||||||||||||||]]></description><Point><coordinates>-107.806870177367,37.6409842559718,0</coordinates></Point></Placemark><Placemark><name>87a98f08 82ec 4a31 ad17 27c18127bb44</name><description><![CDATA[28826|||||||||||||||||||]]></description><Point><coordinates>-107.806584927444,37.6418112739569,0</coordinates></Point></Placemark><Placemark><name>8892f8f6 26df 4815 818b 845f1215c5ca</name><description><![CDATA[28827|||||||||||||||||||]]></description><Point><coordinates>-107.806467021605,37.6424646774162,0</coordinates></Point></Placemark><Placemark><name>e99af6c1 e672 44ae 9bed 2c836033723b</name><description><![CDATA[28828|||||||||||||||||||]]></description><Point><coordinates>-107.806959264936,37.6427407166112,0</coordinates></Point></Placemark><Placemark><name>fbabe593 3846 4fc9 b5f6 34815ae4dd22</name><description><![CDATA[28829|||||||||||||||||||]]></description><Point><coordinates>-107.80733480555,37.6437085953877,0</coordinates></Point></Placemark><Placemark><name>6662682c 3124 4952 a244 1a9e42665d79</name><description><![CDATA[28851|||||||||||||||||||]]></description><Point><coordinates>-107.807224046344,37.6401421527224,0</coordinates></Point></Placemark><Placemark><name>b5c17ce0 f569 43dc a0f1 b2d267b95d03</name><description><![CDATA[28784|A5.21||||||||||||||None||||]]></description><Point><coordinates>-107.800384414471,37.6568996430077,0</coordinates></Point></Placemark><Placemark><name>910ece9a 9027 4e06 b1fd 3e16b7b5064b</name><description><![CDATA[28785|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.801301427633,37.6563844355649,0</coordinates></Point></Placemark><Placemark><name>aab6702e 4688 4892 9899 956af271d35d</name><description><![CDATA[28786|A2.1||||||||||||||None||||]]></description><Point><coordinates>-107.802081997106,37.6559268392015,0</coordinates></Point></Placemark><Placemark><name>06cb99a4 2023 4c67 b44e a5d6dfaa3eb1</name><description><![CDATA[28788|A1.1||||||||||||||None||||]]></description><Point><coordinates>-107.803986389118,37.6571669414356,0</coordinates></Point></Placemark><Placemark><name>24d201bc 047e 472b ad46 1f5c9dd8aed7</name><description><![CDATA[28789|A1.1||||||||||||||None||||]]></description><Point><coordinates>-107.802907329623,37.6569133852782,0</coordinates></Point></Placemark><Placemark><name>086593f7 85dd 446c b56a d8fc377ede91</name><description><![CDATA[28790|A6.2||||||||||||||None||||]]></description><Point><coordinates>-107.802490457493,37.6568418900378,0</coordinates></Point></Placemark><Placemark><name>5dcbd43f 9431 43c5 90f8 2587f6f68bc5</name><description><![CDATA[28792|A1.1||||||||||||||None||||]]></description><Point><coordinates>-107.804995451019,37.6573916424451,0</coordinates></Point></Placemark><Placemark><name>f704598b 5808 43cb bd12 4f8f88fd9a50</name><description><![CDATA[28794|A2.21||||||||||||||None||||]]></description><Point><coordinates>-107.80170470589,37.6593627341674,0</coordinates></Point></Placemark><Placemark><name>82abbe50 c59a 4488 85f5 8d4fb05cf45a</name><description><![CDATA[28795|A5.1||||||||||||||None||||]]></description><Point><coordinates>-107.800448442037,37.6589670263418,0</coordinates></Point></Placemark><Placemark><name>677265b3 2e97 4cb2 824d bdd43bc0a78d</name><description><![CDATA[28797|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.801273531432,37.6599453469678,0</coordinates></Point></Placemark><Placemark><name>5397010b 2f4c 4dcb 923c 69696f142908</name><description><![CDATA[28800|A5.21|||||||5|||40||||Phone|Cable|||]]></description><Point><coordinates>-107.808106330316,37.658533820696,0</coordinates></Point></Placemark><Placemark><name>a66f8674 19ad 4dc1 ae35 3db51a23ab89</name><description><![CDATA[28801|A1.1||||||||||||||None||||]]></description><Point><coordinates>-107.80669929079,37.6577345567506,0</coordinates></Point></Placemark><Placemark><name>0f601216 3d39 4be7 b8d1 2b06ba9d24bd</name><description><![CDATA[28802|A1.1||||||||||||||None||||]]></description><Point><coordinates>-107.805828695684,37.657518140522,0</coordinates></Point></Placemark><Placemark><name>f42d99cc 2810 4f9d bb65 f27070426b34</name><description><![CDATA[28803|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.809430710156,37.6600077579308,0</coordinates></Point></Placemark><Placemark><name>4ae050fb 15c1 4187 b42e 6f93480b29c4</name><description><![CDATA[28804|A6.21||||||||||||||None||||]]></description><Point><coordinates>-107.808991026272,37.659203231304,0</coordinates></Point></Placemark><Placemark><name>61a55ef8 2e0d 4cd4 bbc1 4561461f026b</name><description><![CDATA[28805|A5.2||||||||||||||Cable||||]]></description><Point><coordinates>-107.808951712189,37.6590501609608,0</coordinates></Point></Placemark><Placemark><name>209d68b3 6513 4a7a 916f cc2148a27cf1</name><description><![CDATA[28806|A5.21||||||||||||||None||||]]></description><Point><coordinates>-107.809873644543,37.6586941270442,0</coordinates></Point></Placemark><Placemark><name>e9dfb9d8 b9be 4861 8fd0 b01565183be0</name><description><![CDATA[28808|A5.31|UA1|||||||||||||None||||]]></description><Point><coordinates>-107.812211814011,37.6583212012452,0</coordinates></Point></Placemark><Placemark><name>cb64e21f 885e 4187 a624 72cf1c475d93</name><description><![CDATA[28824|A5.1|UA1||||||5|||40||||Phone||||]]></description><Point><coordinates>-107.808275162744,37.6584068786284,0</coordinates></Point></Placemark><Placemark><name>86ca5452 e37f 4630 a2c6 9a95c28a668e</name><description><![CDATA[31423|||||||||||||||||||]]></description><Point><coordinates>-107.798726537256,37.663469579367,0</coordinates></Point></Placemark><Placemark><name>94c88fa2 c357 480a a68b cde771ee2727</name><description><![CDATA[28758|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.798801376106,37.6626915021016,0</coordinates></Point></Placemark><Placemark><name>22b64b15 dbfb 47c3 bae7 caa652b85df7</name><description><![CDATA[28765|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.799369200382,37.662056774744,0</coordinates></Point></Placemark><Placemark><name>6f0113a6 041f 4ae3 acfa f0491c97ecaf</name><description><![CDATA[28798|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.800606640475,37.6607276261632,0</coordinates></Point></Placemark><Placemark><name>347ba523 ed05 458d 90be 7293f9588b12</name><description><![CDATA[28799|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.799935739721,37.6613808616606,0</coordinates></Point></Placemark><Placemark><name>bb8e37e3 915b 439a af60 43788dfdc9ac</name><description><![CDATA[28807|M5-5|M5-5|A5.31|A5.21|||||||||||Cable||||]]></description><Point><coordinates>-107.81075141203,37.6602510071902,0</coordinates></Point></Placemark><Placemark><name>53b96116 9112 4b9e 8335 eeaaa9f0e7d4</name><description><![CDATA[28809|A1.11||||||||||||||Phone||||]]></description><Point><coordinates>-107.814690592053,37.6646788550727,0</coordinates></Point></Placemark><Placemark><name>ac75dc2a 8011 44f4 9adc 5067d4432f07</name><description><![CDATA[28810|A1.11||||||||||||||Phone||||]]></description><Point><coordinates>-107.814367951231,37.6643060987545,0</coordinates></Point></Placemark><Placemark><name>256a0fa3 3873 4a07 9542 23a8932b2e5d</name><description><![CDATA[28811|A3.4||||||||||||||Phone||||]]></description><Point><coordinates>-107.81379585806,37.6636910076136,0</coordinates></Point></Placemark><Placemark><name>ca29d49d f6f2 4f41 8838 d3c214a6dcd8</name><description><![CDATA[28812|A1.11||||||||||||||Phone||||]]></description><Point><coordinates>-107.813089476271,37.6632021826234,0</coordinates></Point></Placemark><Placemark><name>88a95ec5 f99e 4439 935a 860b119f1cac</name><description><![CDATA[28813|A1.11||||||||||||||Phone||||]]></description><Point><coordinates>-107.812069627821,37.6626343612391,0</coordinates></Point></Placemark><Placemark><name>89cca1b6 3a8e 4b0a b803 783a7ce16da9</name><description><![CDATA[28814|A1.11||||||||||||||Phone||||]]></description><Point><coordinates>-107.811189049515,37.6620995045036,0</coordinates></Point></Placemark><Placemark><name>f6276f43 08e7 4a13 8b5d 7c10e4ec8622</name><description><![CDATA[28815|A3.4||||||||||||||None||||]]></description><Point><coordinates>-107.810098195198,37.6614698781386,0</coordinates></Point></Placemark><Placemark><name>d848649c 5064 48aa a220 e9ffeabb8f59</name><description><![CDATA[28816|A1.11|METER|||||1955|5|||35|||CREOSOTE|Phone|||WC|]]></description><Point><coordinates>-107.815092066901,37.6653632379532,0</coordinates></Point></Placemark><Placemark><name>1e0dee74 3be6 4094 b669 fb5efca78da3</name><description><![CDATA[31424|||||||||||||||||||]]></description><Point><coordinates>-107.792446299725,37.6603528836741,0</coordinates></Point></Placemark><Placemark><name>595df34b 3ac6 474e 84d1 ce5c108c573a</name><description><![CDATA[28748|A4.1||||||||||||||None||||]]></description><Point><coordinates>-107.791969335959,37.6596657536596,0</coordinates></Point></Placemark><Placemark><name>7688ddfd 8e91 4994 9096 00fbe3d94714</name><description><![CDATA[28749|A4.1||||||||||||||None||||]]></description><Point><coordinates>-107.792931994478,37.6595068573319,0</coordinates></Point></Placemark><Placemark><name>f4468f01 bd2d 4801 be91 0b856c958c69</name><description><![CDATA[28750|A6.2||||||||||||||None||||]]></description><Point><coordinates>-107.793028818645,37.6592851919522,0</coordinates></Point></Placemark><Placemark><name>0fe8e897 0531 427c a70e bd24e368d421</name><description><![CDATA[28751|A5.1||||||||||||||None||||]]></description><Point><coordinates>-107.792985553795,37.6590030762246,0</coordinates></Point></Placemark><Placemark><name>2f4fa2a5 e236 48c0 b0fb 27dba3200ac5</name><description><![CDATA[32889|||||||||||||||||||]]></description><Point><coordinates>-107.795948475196,37.6615477673833,0</coordinates></Point></Placemark><Placemark><name>88445c0f a459 4ee5 bb36 c5b3fb5aa5cd</name><description><![CDATA[32890|A5.1||||||2006|5|||40|||||||DF|Wood]]></description><Point><coordinates>-107.795913960708,37.661109029869,0</coordinates></Point></Placemark><Placemark><name>f926d68b 83b4 453a b2ae 4cdf89354b82</name><description><![CDATA[32888|A6.2||||||2006|5|||40|||||||DF|]]></description><Point><coordinates>-107.795714111468,37.6617802055177,0</coordinates></Point></Placemark><Placemark><name>c16d4c1b 236d 453e a9e2 593063b1874d</name><description><![CDATA[28747|A2.1||||||||||||||None||||]]></description><Point><coordinates>-107.791995345322,37.6605086326115,0</coordinates></Point></Placemark><Placemark><name>dcbfc9e6 0437 4782 9189 0d27cd126d5e</name><description><![CDATA[28752|A2.1||||||||||||||None||||]]></description><Point><coordinates>-107.792726563969,37.6618075166416,0</coordinates></Point></Placemark><Placemark><name>da2237eb b61b 4e5b 819c a7b92ce16f00</name><description><![CDATA[28753|A3.4|UA2|||||||||||||None||||]]></description><Point><coordinates>-107.792237890748,37.66164947628,0</coordinates></Point></Placemark><Placemark><name>32a1c2ae fd0e 43f3 948b e3fba4c11475</name><description><![CDATA[28754|A2.1|A5-1|A3.4||||2006|6|5||35|40|||None|||DF|Wood]]></description><Point><coordinates>-107.795562929137,37.6618949694373,0</coordinates></Point></Placemark><Placemark><name>c0c7b2fe 303d 447e 948a 4ea428b15d23</name><description><![CDATA[28755|A2.1||||||||||||||None||||]]></description><Point><coordinates>-107.79469367491,37.6617223992917,0</coordinates></Point></Placemark><Placemark><name>46e2a38e 49c2 4861 803d 70afdcf78012</name><description><![CDATA[28756|M5-5|A5.1|A5.1||||||||||||None||||]]></description><Point><coordinates>-107.794142201634,37.6617688856364,0</coordinates></Point></Placemark><Placemark><name>d9a8db53 d547 455e ba3d 1e662c315be0</name><description><![CDATA[28757|A1.1|METER|||||1979|7|||30|||PENTA|None|||WC|]]></description><Point><coordinates>-107.793622974542,37.661740565604,0</coordinates></Point></Placemark><Placemark><name>9d04b44b 4a1d 422f aa9b 02625330bc64</name><description><![CDATA[28759|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.798163279416,37.663289140778,0</coordinates></Point></Placemark><Placemark><name>c3cc97f2 5ea4 4b45 9e19 452e5827fe18</name><description><![CDATA[28760|A1.11|A5.2|||||||||||||None||||]]></description><Point><coordinates>-107.797662131199,37.6638456361929,0</coordinates></Point></Placemark><Placemark><name>7b63ceb1 f641 4f03 95d1 1565271ea325</name><description><![CDATA[28761|A2.1||||||||||||||None||||]]></description><Point><coordinates>-107.796121956413,37.6620928286501,0</coordinates></Point></Placemark><Placemark><name>d094112d eb08 4e18 8e2b 793ecd581f8f</name><description><![CDATA[28762|A2.1||||||||||||||None||||]]></description><Point><coordinates>-107.797079310924,37.6628790360625,0</coordinates></Point></Placemark><Placemark><name>5f29ce84 7932 413e b309 6a70494b6a74</name><description><![CDATA[28763|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.796925243574,37.6646017899481,0</coordinates></Point></Placemark><Placemark><name>ca9f74d5 f3da 4123 a369 b5d1ec45ed06</name><description><![CDATA[28764|A2.21|METER|||||1983|6|||30|||PENTA|None|||DF|]]></description><Point><coordinates>-107.79628821893,37.6652351101468,0</coordinates></Point></Placemark><Placemark><name>82873819 24eb 458c 845f 287b2950bfe7</name><description><![CDATA[28766|A1.11||||||||||||||None||||]]></description><Point><coordinates>-107.796073956228,37.666129387831,0</coordinates></Point></Placemark><Placemark><name>f90c122e a30d 41ca ab2d ff9a555b6f7b</name><description><![CDATA[28767|A5.21|UA1||||||5|||40||||||||]]></description><Point><coordinates>-107.796022191295,37.6664547897361,0</coordinates></Point></Placemark></Folder></kml>'
SET @utilityId = 4

EXEC up_Utility_Asset_Distribution_ImportData @kmlFileName, @kmlContent, @utilityId

*/
